import type { KnowledgeDetail } from '@/types/knowledge';

/**
 * 高级查询知识点数据
 */
export const advancedQueryKnowledge: KnowledgeDetail[] = [
	{
		id: 1,
		title: '子查询（Subquery）',
		description: '子查询是在主查询中嵌套的查询，用于获取中间结果或进行复杂的数据筛选。',
		icon: '🔍',
		categoryId: 'sql-advanced-query',
		categoryName: '高级查询',
		categoryIcon: '🚀',
		difficulty: '中级',
		color: 'linear-gradient(135deg, #667eea 0%, #764ba2 100%)',
		headerBg: 'linear-gradient(135deg, #2c3e50 0%, #34495e 100%)',
		tagBg: 'linear-gradient(135deg, #3498db 0%, #2980b9 100%)',
		tagColor: '#ffffff',
		difficultyBg: 'linear-gradient(135deg, #f39c12 0%, #e67e22 100%)',
		keyPoints: [
			{
				point: '标量子查询：返回单个值的子查询，可以在SELECT、WHERE、HAVING中使用。',
				code: `-- 在SELECT中使用标量子查询
SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

-- 在WHERE中使用标量子查询
SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products);

-- 在HAVING中使用标量子查询
SELECT category_id, COUNT(*) 
FROM products 
GROUP BY category_id
HAVING COUNT(*) > (SELECT AVG(product_count) FROM (
    SELECT category_id, COUNT(*) AS product_count 
    FROM products 
    GROUP BY category_id
) AS sub);`
			},
			{
				point: '行子查询：返回单行多列的子查询，通常与行比较运算符一起使用。',
				code: `-- 行子查询示例
SELECT * FROM users 
WHERE (age, salary) = (
    SELECT MAX(age), MAX(salary) 
    FROM users
);

-- 查找年龄和薪资都最大的员工
SELECT * FROM employees 
WHERE (department_id, salary) IN (
    SELECT department_id, MAX(salary) 
    FROM employees 
    GROUP BY department_id
);`
			},
			{
				point: '表子查询：返回多行多列的子查询，可以作为临时表使用。',
				code: `-- 表子查询作为临时表
SELECT * FROM (
    SELECT user_id, SUM(amount) AS total 
    FROM orders 
    GROUP BY user_id
) AS user_totals
WHERE total > 1000;

-- 在FROM子句中使用
SELECT u.name, o.order_count 
FROM users u
JOIN (
    SELECT user_id, COUNT(*) AS order_count 
    FROM orders 
    GROUP BY user_id
) AS o ON u.id = o.user_id;`
			},
			{
				point: '相关子查询：子查询引用外层查询的列，需要对外层查询的每一行执行子查询。',
				code: `-- 相关子查询：查找每个部门中薪资最高的员工
SELECT e1.* 
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary) 
    FROM employees e2 
    WHERE e2.department_id = e1.department_id
);

-- 查找每个类别中价格最高的产品
SELECT p1.* 
FROM products p1
WHERE p1.price = (
    SELECT MAX(p2.price) 
    FROM products p2 
    WHERE p2.category_id = p1.category_id
);`
			},
			{
				point: 'EXISTS和NOT EXISTS：用于检查子查询是否返回结果，常用于关联查询。',
				code: `-- EXISTS：存在即返回
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id 
    AND o.total_amount > 1000
);

-- NOT EXISTS：不存在才返回
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id
);
-- 等价于：查找没有订单的用户

-- EXISTS通常比IN性能更好，特别是子查询返回大量数据时`
			}
		]
	},
	{
		id: 2,
		title: '聚合函数',
		description: '聚合函数对一组值执行计算并返回单个值，常用于统计和汇总数据。',
		icon: '📊',
		categoryId: 'sql-advanced-query',
		categoryName: '高级查询',
		categoryIcon: '🚀',
		difficulty: '中级',
		color: 'linear-gradient(135deg, #667eea 0%, #764ba2 100%)',
		headerBg: 'linear-gradient(135deg, #2c3e50 0%, #34495e 100%)',
		tagBg: 'linear-gradient(135deg, #3498db 0%, #2980b9 100%)',
		tagColor: '#ffffff',
		difficultyBg: 'linear-gradient(135deg, #f39c12 0%, #e67e22 100%)',
		keyPoints: [
			{
				point: '常用聚合函数：COUNT、SUM、AVG、MAX、MIN用于基本统计。',
				code: `-- COUNT：计数
SELECT COUNT(*) FROM users;                    -- 总记录数
SELECT COUNT(DISTINCT city) FROM users;        -- 不重复城市数
SELECT COUNT(email) FROM users;                -- 非NULL的email数

-- SUM：求和
SELECT SUM(amount) FROM orders;
SELECT SUM(price * quantity) AS total FROM order_items;

-- AVG：平均值
SELECT AVG(age) FROM users;
SELECT AVG(price) FROM products WHERE category_id = 1;

-- MAX/MIN：最大/最小值
SELECT MAX(price) FROM products;
SELECT MIN(created_at) FROM orders;`
			},
			{
				point: 'GROUP BY分组：将数据分组，对每组分别应用聚合函数。',
				code: `-- 按类别统计产品数量
SELECT category_id, COUNT(*) AS product_count 
FROM products 
GROUP BY category_id;

-- 按用户统计订单总金额
SELECT user_id, SUM(total_amount) AS total_spent 
FROM orders 
GROUP BY user_id;

-- 多列分组
SELECT category_id, status, COUNT(*) 
FROM products 
GROUP BY category_id, status;`
			},
			{
				point: 'HAVING过滤分组：HAVING用于过滤分组后的结果，WHERE用于过滤分组前。',
				code: `-- HAVING：过滤分组后的结果
SELECT category_id, COUNT(*) AS count 
FROM products 
GROUP BY category_id
HAVING COUNT(*) > 10;  -- 只显示产品数大于10的类别

-- WHERE vs HAVING
SELECT category_id, AVG(price) AS avg_price 
FROM products 
WHERE price > 100              -- 先过滤单个产品
GROUP BY category_id
HAVING AVG(price) > 500;       -- 再过滤分组结果

-- 执行顺序：WHERE -> GROUP BY -> 聚合函数 -> HAVING`
			},
			{
				point: 'ROLLUP和CUBE：生成分组汇总的多个层级。',
				code: `-- ROLLUP：生成分组和小计
SELECT category_id, SUM(price) AS total 
FROM products 
GROUP BY ROLLUP(category_id);
-- 结果包含：每个类别的总计 + 所有类别的总计

-- CUBE：生成所有可能的分组组合
SELECT category_id, status, COUNT(*) 
FROM products 
GROUP BY CUBE(category_id, status);
-- 结果包含：所有组合的统计`

			},
			{
				point: '窗口函数：在保留行数据的同时进行聚合计算。',
				code: `-- 窗口函数：计算每行的累计和
SELECT 
    id,
    amount,
    SUM(amount) OVER (ORDER BY id) AS running_total
FROM orders;

-- 计算每个类别的平均价格，同时保留原始行
SELECT 
    id,
    name,
    price,
    category_id,
    AVG(price) OVER (PARTITION BY category_id) AS avg_price_in_category
FROM products;`
			}
		]
	},
	{
		id: 3,
		title: 'CASE WHEN条件表达式',
		description: 'CASE WHEN是SQL中的条件表达式，类似于编程语言中的if-else语句。',
		icon: '🔀',
		categoryId: 'sql-advanced-query',
		categoryName: '高级查询',
		categoryIcon: '🚀',
		difficulty: '中级',
		color: 'linear-gradient(135deg, #667eea 0%, #764ba2 100%)',
		headerBg: 'linear-gradient(135deg, #2c3e50 0%, #34495e 100%)',
		tagBg: 'linear-gradient(135deg, #3498db 0%, #2980b9 100%)',
		tagColor: '#ffffff',
		difficultyBg: 'linear-gradient(135deg, #f39c12 0%, #e67e22 100%)',
		keyPoints: [
			{
				point: '简单CASE表达式：根据值进行匹配，类似于switch语句。',
				code: `-- 简单CASE表达式
SELECT 
    name,
    status,
    CASE status
        WHEN 'active' THEN '活跃'
        WHEN 'inactive' THEN '非活跃'
        WHEN 'pending' THEN '待审核'
        ELSE '未知'
    END AS status_text
FROM users;

-- 在WHERE中使用
SELECT * FROM orders 
WHERE CASE 
    WHEN total_amount > 1000 THEN 'high'
    WHEN total_amount > 500 THEN 'medium'
    ELSE 'low'
END = 'high';`
			},
			{
				point: '搜索CASE表达式：根据条件进行判断，类似于if-else语句。',
				code: `-- 搜索CASE表达式
SELECT 
    name,
    age,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age < 60 THEN '成年人'
        ELSE '老年人'
    END AS age_group
FROM users;

-- 多条件判断
SELECT 
    product_name,
    price,
    CASE 
        WHEN price > 1000 THEN '高价位'
        WHEN price > 500 THEN '中价位'
        WHEN price > 100 THEN '低价位'
        ELSE '超低价位'
    END AS price_category
FROM products;`
			},
			{
				point: 'CASE在聚合函数中使用：根据不同条件进行不同的聚合计算。',
				code: `-- CASE在COUNT中使用
SELECT 
    category_id,
    COUNT(*) AS total,
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
    COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count
FROM products 
GROUP BY category_id;

-- CASE在SUM中使用
SELECT 
    user_id,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_total,
    SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending_total
FROM orders 
GROUP BY user_id;`
			},
			{
				point: 'CASE在UPDATE中使用：根据条件更新不同的值。',
				code: `-- 根据条件更新不同值
UPDATE products 
SET price = CASE 
    WHEN category_id = 1 THEN price * 1.1  -- 类别1涨价10%
    WHEN category_id = 2 THEN price * 0.9  -- 类别2降价10%
    ELSE price                              -- 其他不变
END;

-- 根据多个条件更新
UPDATE users 
SET 
    level = CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 60 THEN 'C'
        ELSE 'D'
    END;`
			},
			{
				point: '嵌套CASE：可以在CASE中嵌套另一个CASE，实现复杂的条件逻辑。',
				code: `-- 嵌套CASE示例
SELECT 
    name,
    age,
    gender,
    CASE 
        WHEN age < 18 THEN 
            CASE WHEN gender = 'M' THEN '男孩' ELSE '女孩' END
        WHEN age < 60 THEN 
            CASE WHEN gender = 'M' THEN '男人' ELSE '女人' END
        ELSE 
            CASE WHEN gender = 'M' THEN '老人' ELSE '老妇' END
    END AS description
FROM users;

-- 注意：嵌套CASE可读性较差，应谨慎使用`
			}
		]
	},
	{
		id: 4,
		title: 'UNION集合操作',
		description: 'UNION用于合并多个SELECT查询的结果集，去除重复行或保留所有行。',
		icon: '🔗',
		categoryId: 'sql-advanced-query',
		categoryName: '高级查询',
		categoryIcon: '🚀',
		difficulty: '中级',
		color: 'linear-gradient(135deg, #667eea 0%, #764ba2 100%)',
		headerBg: 'linear-gradient(135deg, #2c3e50 0%, #34495e 100%)',
		tagBg: 'linear-gradient(135deg, #3498db 0%, #2980b9 100%)',
		tagColor: '#ffffff',
		difficultyBg: 'linear-gradient(135deg, #f39c12 0%, #e67e22 100%)',
		keyPoints: [
			{
				point: 'UNION：合并两个查询结果，自动去除重复行。',
				code: `-- UNION：合并两个查询，去除重复
SELECT name FROM users
UNION
SELECT name FROM admins;
-- 结果：两个表中所有不重复的name

-- UNION要求：两个查询的列数相同，数据类型兼容
SELECT id, name FROM users
UNION
SELECT id, name FROM customers;`
			},
			{
				point: 'UNION ALL：合并两个查询结果，保留所有行（包括重复）。',
				code: `-- UNION ALL：合并两个查询，保留重复
SELECT name FROM users
UNION ALL
SELECT name FROM admins;
-- 结果：两个表中所有的name（包括重复）

-- UNION ALL比UNION更快，因为不需要去重
-- 如果确定没有重复，使用UNION ALL性能更好`
			},
			{
				point: 'INTERSECT：返回两个查询结果的交集（同时存在于两个结果集中的行）。',
				code: `-- INTERSECT：交集（某些数据库支持）
SELECT name FROM users
INTERSECT
SELECT name FROM admins;
-- 结果：既在users表中又在admins表中的name

-- MySQL不支持INTERSECT，可以用INNER JOIN实现
SELECT DISTINCT u.name 
FROM users u
INNER JOIN admins a ON u.name = a.name;`
			},
			{
				point: 'EXCEPT/MINUS：返回第一个查询结果减去第二个查询结果的差集。',
				code: `-- EXCEPT：差集（SQL Server/PostgreSQL）
SELECT name FROM users
EXCEPT
SELECT name FROM admins;
-- 结果：在users表中但不在admins表中的name

-- MySQL不支持EXCEPT，可以用NOT IN或LEFT JOIN实现
SELECT name FROM users 
WHERE name NOT IN (SELECT name FROM admins);

-- 或使用LEFT JOIN
SELECT DISTINCT u.name 
FROM users u
LEFT JOIN admins a ON u.name = a.name
WHERE a.name IS NULL;`
			},
			{
				point: '多个UNION：可以连接多个查询，按顺序执行。',
				code: `-- 多个UNION连接
SELECT 'user' AS type, name FROM users
UNION ALL
SELECT 'admin' AS type, name FROM admins
UNION ALL
SELECT 'guest' AS type, name FROM guests
ORDER BY type, name;

-- 注意：ORDER BY只能放在最后一个查询后面
-- 注意：列名以第一个查询的列名为准`
			}
		]
	}
];

